/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package classes;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Alvaro Los datos que aparecen en esta clase pertencen todos la tabla
 * user. Cada clase representara a una tabla
 */
public class product {

    private int id;
    String team;
    String name;
    float price;
    String description;
    int purchasedtimes;
    String dir;
    float stock;

    public product(String team, String name, float price, String description, int purchasedtimes, String dir, float stock) {
        this.team = team;
        this.name = name;
        this.price = price;
        this.description = description;
        this.purchasedtimes = purchasedtimes;
        this.dir = dir;
        this.stock = stock;
    }

    public product() {
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getDir() {
        return dir;
    }

    public void setDir(String dir) {
        this.dir = dir;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    public int getPurchasedtimes() {
        return purchasedtimes;
    }

    public void setPurchasedtimes(int purchasedtimes) {
        this.purchasedtimes = purchasedtimes;
    }

    public float getStock() {
        return stock;
    }

    public void setStock(float stock) {
        this.stock = stock;
    }

    public String getTeam() {
        return team;
    }

    public void setTeam(String team) {
        this.team = team;
    }

    /**
     * ************ DB *************************
     */
    public boolean guardar() throws SQLException {

        ObjetoDB o = new ObjetoDB();

        String price2 = Float.toString(price);
        String purchasedtimes2 = Integer.toString(purchasedtimes);
        String stock2 = Float.toString(stock);

        o.setTabla("product");
        o.addCampo("team", team);
        o.addCampo("name", name);
        o.addCampo("price", price2);
        o.addCampo("description", description);
        o.addCampo("purchasedtimes", purchasedtimes2);
        o.addCampo("dir", dir);
        o.addCampo("stock", stock2);


        AdminDB adb = new AdminDB();
        return adb.guardar(o);
    }

    public void getByName(Connection conn, String name) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE name='" + name + "';");
                System.out.println("Listado de productos por nombre...");
            }
            rset = stmt.executeQuery();

            if (rset.next()) {

                this.setId(rset.getInt("ID"));
                this.team = rset.getString("team");
                this.name = rset.getString("name");
                this.price = rset.getFloat("price");
                this.description = rset.getString("description");
                this.purchasedtimes = rset.getInt("purchasedtimes");
                this.dir = rset.getString("dir");
                this.stock = rset.getFloat("stock");
            }
            
            if (stmt != null) {
                stmt.close();
            }

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
        } catch (Exception e) {
            Logger.getLogger("GetByName").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetByName").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("GetByName").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean borrar(String name) throws SQLException {
        AdminDB adb = new AdminDB();
        int cpID = adb.getID("product", "name='" + name + "'");
        ObjetoDB o = new ObjetoDB();
        o.setTabla("product");
        o.addCampo("name", name);
        return adb.borrar(o);

    }

    public boolean borrarByID(String id) throws SQLException {
        AdminDB adb = new AdminDB();
        int cpID = adb.getID("product", "id='" + id + "'");
        ObjetoDB o = new ObjetoDB();
        o.setTabla("product");
        o.addCampo("id", id);
        return adb.borrar(o);
    }

    public boolean borrarTodosProductos() throws SQLException {
        AdminDB adb = new AdminDB();
        ObjetoDB o = new ObjetoDB();
        o.setTabla("product");
        return adb.vaciarTabla(o);
    }

    public List<product> getAllProductos(Connection conn) throws SQLException{
        ResultSet rset = null;
        try {

            PreparedStatement stmt = null;
            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product`");
                System.out.println("Listado de productos ...");
            }
            rset = stmt.executeQuery();
            List<product> prod = new ArrayList<product>();
            while (rset.next()) {
                product p = new product();
                p.setId(rset.getInt("ID"));
                p.setTeam(rset.getString("team"));
                p.setName(rset.getString("name"));
                p.setPrice(rset.getFloat("price"));
                p.setDescription(rset.getString("description"));
                p.setPurchasedtimes(rset.getInt("purchasedtimes"));
                p.setDir(rset.getString("dir"));
                p.setStock(rset.getFloat("stock"));


                prod.add(p);
            }
            
            if (stmt != null) {
                stmt.close();
            }

            return prod;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Producto").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("RecoverName").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("RecoverName").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public List<product> searchProd(Connection conn, String busqueda, String by) throws SQLException {
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE " + by + " LIKE '%" + busqueda + "%'");
                System.out.println("Busqueda de productos ...");
            }
            rset = stmt.executeQuery();
            List<product> prod = new ArrayList<product>();
            while (rset.next()) {
                product p = new product();
                p.setId(rset.getInt("ID"));
                p.setTeam(rset.getString("team"));
                p.setName(rset.getString("name"));
                p.setPrice(rset.getFloat("price"));
                p.setDescription(rset.getString("description"));
                p.setPurchasedtimes(rset.getInt("purchasedtimes"));
                p.setDir(rset.getString("dir"));
                p.setStock(rset.getFloat("stock"));


                prod.add(p);
            }
            if (stmt != null) {
                stmt.close();
            }
            

            return prod;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Producto").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("SearchProd").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("SearchProd").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public boolean changeProdInfo(Connection conn, String teamMod, String nameMod, String descriptionMod, float priceMod, float stockMod, String dirMod, Integer idMod) throws SQLException{
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("UPDATE product SET team='" + teamMod + "', description='" + descriptionMod + "', name='" + nameMod + "', price=" + priceMod + ", stock=" + stockMod + ""
                        + ", dir='" + dirMod + "' WHERE id=" + idMod + "");
            }

            stmt.executeUpdate();

            stmt.close();

            return true;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
        } catch (Exception e) {
            System.out.println("Error inesperado: " + e.getMessage());
        }
        finally {
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("ChangeProdInfo").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
        return false;
    }

    public List<product> showSomeProducts(Connection conn, int option) throws SQLException{
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt && option == 1) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE price < 100");
            }
            if (null == stmt && option == 2) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE price > 300");
            }
            if (null == stmt && option == 3) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE purchasedtimes > 1");
            }
            if (null == stmt && option == 4) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE description LIKE '%2012%'");
            }
            if (null == stmt && option == 5) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE description LIKE '%2011%'");
            }
            if (null == stmt && option == 6) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE description LIKE '%2010%'");
            }
            if (null == stmt && option == 7) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE description LIKE '%2009%'");
            }
            rset = stmt.executeQuery();


            List<product> prod = new ArrayList<product>();

            while (rset.next()) {
                product p = new product();
                p.setId(rset.getInt("ID"));
                p.setTeam(rset.getString("team"));
                p.setName(rset.getString("name"));
                p.setPrice(rset.getFloat("price"));
                p.setDescription(rset.getString("description"));
                p.setPurchasedtimes(rset.getInt("purchasedtimes"));
                p.setDir(rset.getString("dir"));
                p.setStock(rset.getFloat("stock"));
                prod.add(p);
            }
            
            if (stmt != null) {
                stmt.close();
            }
            

            return prod;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return null;
        } catch (Exception e) {
            Logger.getLogger("Producto").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return null;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("ShowSomeProducts").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("ShowSImeProducts").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    public float comprobarStock(Connection conn, String producto) throws SQLException{
        ResultSet rset = null;
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("SELECT * FROM `F1shopOnline`.`product` WHERE name='" + producto + "'");
            }
            rset = stmt.executeQuery();
            while (rset.next()) {

                return rset.getFloat("stock");
            }
            if (stmt != null) {
                stmt.close();
            }
            
            System.out.println("Operacion de reducir stock empezando, conexion abierta!");
            return 0;

        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return 0;
        } catch (Exception e) {
            Logger.getLogger("Producto").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return 0;
        }
        finally {
            if (rset != null) {
                try {
                    rset.close();
                } catch (SQLException e) {
                    Logger.getLogger("ComprobarStock").log(Level.SEVERE, "ERROR AL CERRAR RSET: " + e.getLocalizedMessage());
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("ComprobarStock").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }
    
    
    //Reducir stock y aumentar el campo purchasedtimes
    public void reducirStock(Connection conn, String product, float qty) throws SQLException{
        try {
            PreparedStatement stmt = null;

            if (null == stmt) {
                stmt = conn.prepareStatement("UPDATE `F1shopOnline`.`product` SET stock=stock - " + qty + ", purchasedtimes= purchasedtimes+" + qty + " WHERE name='" + product + "'");
            }
            stmt.executeUpdate();
            stmt.close();
            System.out.println("Operacion de reducir stock terminada, conexion cerrada!");


        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
        } catch (Exception e) {
            System.out.println("Error inesperado: " + e.getMessage());
        }
        finally {
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("ReducirStock").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }

    //Script para cargar todos los productos 
    public boolean loadAllProducts(Connection conn) throws SQLException {
        try {
            Statement stmt = conn.createStatement();

            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (1,'Ferrari','Ferrari 2001',240,'Ferrari F1 year 2001',6,'images/carimages/ferrari2001.jpg',4);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (2,'Ferrari','Ferrari 2002',300,'Ferrari F1 year 2002',2,'images/carimages/ferrari2002.jpg',8);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (3,'Ferrari','Ferrari 2003',340,'Ferrari F1 year 2003',2,'images/carimages/ferrari2003.jpg',8);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (4,'Ferrari','Ferrari 2007',450,'Ferrari F1 year 2007',5,'images/carimages/ferrari2007.jpg',5);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (5,'Ferrari','Ferrari 2012',300,'Ferrari F1 year 2012',10,'images/carimages/ferrari2012.jpg',0);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (6,'Williams','Williams 2004',140,'Wiiliams F1 year 2004',0,'images/carimages/williams2004.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (7,'Williams','Williams 2008',160,'Williams F1 year 2008',0,'images/carimages/williams2008.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (8,'Williams','Williams 2009',200,'Williams F1 year 2009',0,'images/carimages/williams2009.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (9,'Force India','Force India 2008',70,'Force India F1 year 2008',0,'images/carimages/forceindia2008.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (10,'Force India','Force India 2010',75,'Force India F1 year 2010',0,'images/carimages/forceindia2010.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (11,'Force India','Force India 2011',100,'Force India F1 year 201',0,'images/carimages/forceindia2011.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (12,'Toro Rosso','Toro Rosso 2011',90,'Toro Rosso F1 year 2011',0,'images/carimages/tororosso2011.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (13,'Toro Rosso','Toro Rosso 2009',80,'Toro Rosso F1 year 2009',0,'images/carimages/tororosso2009.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (14,'HRT','HRT 2010',60,'HRT F1 year 2010',0,'images/carimages/hrt2010.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (15,'HRT','HRT 2011',70,'HRT F1 year 2011',0,'images/carimages/hrt2011.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (16,'RedBull','RedBull 2011',400,'RedBull F1 year 2011',0,'images/carimages/redbull2011.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (17,'RedBull','RedBull 2010',490,'RedBull F1 year 2010',0,'images/carimages/redbull2010.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (18,'RedBull','RedBull 2008',250,'RedBull F1 year 2008',0,'images/carimages/redbull2008.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (19,'Mercedes','Mercedes 2010',200,'Mercedes F1 year 2010',0,'images/carimages/mercedes2010.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (20,'Lotus','Lotus 2011',75,'Lotus F1 year 2011',0,'images/carimages/lotus2011.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (21,'Renault','Renault 2010',170,'Renault F1 year 2010',0,'images/carimages/renault2010.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (22,'Renault','Renault 2005',350,'Renault F1 year 2005',0,'images/carimages/renault2005.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (23,'Renault','Renault 2006',430,'Renault F1 year 2006',0,'images/carimages/renault2006.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (24,'Renault','Renault 2011',130,'Renault F1 year 2011',0,'images/carimages/renault2011.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (25,'McClaren','McClaren 2012',340,'McClaren F1 year 2012',0,'images/carimages/mcclaren2012.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (26,'McClaren','McClaren 2008',245,'McClaren F1 year 2008',0,'images/carimages/mcclaren2008.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (27,'McClaren','McClaren 2009',380,'McClaren F1 year 2009',0,'images/carimages/mcclaren2009.jpg',10);");
            stmt.executeUpdate("INSERT INTO `product` (`id`,`team`,`name`,`price`,`description`,`purchasedtimes`,`dir`,`stock`) VALUES (28,'Ferrari','Ferrari 2000',100,'Haddock world champion',0,'images/carimages/haddock.gif',10);");
            if (stmt != null) {
                stmt.close();
            }
            return true;
        } catch (SQLException e) {
            System.out.println("Error de MySQL: " + e.getMessage());
            return false;
        } catch (Exception e) {
            Logger.getLogger("Registrar producto").log(Level.SEVERE, "ERROR: " + e.getLocalizedMessage());
            return false;
        }
        finally {
            
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    Logger.getLogger("loadAllProducts").log(Level.SEVERE, "ERROR AL CERRAR LA CONEXION: " + e.getLocalizedMessage());
                }
            }
        }
    }
}
